package achievement.dao.Impl;

import achievement.dao.SzlThesisDao;
import achievement.domain.SzlBook;
import achievement.domain.SzlThesis;
import cn.hutool.core.util.StrUtil;
import utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class SzlThesisDaoImpl implements SzlThesisDao {
    private Connection con;
    private PreparedStatement psmt;
    private ResultSet rs;
    @Override
    public Long insert(SzlThesis szlThesis) {
        String sql = "insert into szl_thesis\n" +
                "        (thesis_name,thesis_publish,thesis_status,thesis_time,index_type,thesis_belong,thesis_proof) \n" +
                "values(?,?,?,?,?,?,?)";
        Long returnID = null;
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            psmt.setString(1,szlThesis.getThesisName());
            psmt.setString(2,szlThesis.getThesisPublish());
            psmt.setString(3,szlThesis.getThesisStatus());
            psmt.setObject(4,szlThesis.getThesisTime());
            psmt.setString(5,szlThesis.getIndexType());
            psmt.setString(6,szlThesis.getThesisBelong());
            psmt.setString(7,szlThesis.getThesisProof());
            psmt.executeUpdate();
            //获取insert后的自增id
            ResultSet rs = psmt.getGeneratedKeys();
            while(rs.next()){
                returnID = rs.getLong(1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(con,psmt);
        }
        return returnID;
    }

    @Override
    public void updateById(SzlThesis szlThesis) {
        String sql = "update szl_thesis set";
        List<Object> param = new ArrayList<>();
        if (szlThesis == null){
            return;
        }else{
            if(szlThesis.getThesisId() != null){
                sql += " thesis_id = ?,";
                param.add(szlThesis.getThesisId());
            }
            if(szlThesis.getThesisName() != null){
                sql += " thesis_name = ?,";
                param.add(szlThesis.getThesisName());
            }
            if(szlThesis.getType() != null){
                sql += " type = ?,";
                param.add(szlThesis.getType());
            }
            if(szlThesis.getThesisPublish() != null){
                sql += " thesis_publish = ?,";
                param.add(szlThesis.getThesisPublish());
            }
            if(szlThesis.getThesisStatus() != null){
                sql += " thesis_status = ?,";
                param.add(szlThesis.getThesisStatus());
            }
            if(szlThesis.getThesisTime() != null){
                sql += " thesis_time = ?,";
                param.add(szlThesis.getThesisTime());
            }
            if(szlThesis.getIndexType() != null){
                sql += " index_type = ?,";
                param.add(szlThesis.getIndexType());
            }
            if(szlThesis.getThesisBelong() != null){
                sql += " thesis_belong = ?,";
                param.add(szlThesis.getThesisBelong());
            }
            if(szlThesis.getThesisProof() != null){
                sql += " thesis_proof = ?,";
                param.add(szlThesis.getThesisProof());
            }
        }
        sql = StrUtil.strip(sql,null,",");
        sql+=" where thesis_id = ?";
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            for(int i = 0; i < param.size(); i++){
                psmt.setObject(i+1,param.get(i));
            }
            psmt.setLong(param.size()+1,szlThesis.getThesisId());
            psmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(con,psmt);
        }
    }

    @Override
    public void deleteById(Long id) {
        String sql = "delete from szl_thesis where thesis_id = ?";
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            psmt.setLong(1,id);
            psmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(con,psmt);
        }
    }

    @Override
    public SzlThesis selectById(Long id) {
        String sql = "select * from szl_thesis where thesis_id = ?";
        SzlThesis szlThesis = new SzlThesis();
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            psmt.setLong(1,id);
            rs = psmt.executeQuery();
            while (rs.next()){
                szlThesis.setThesisId(rs.getLong(1));
                szlThesis.setThesisName(rs.getString(2));
                szlThesis.setType(rs.getInt(3));
                szlThesis.setThesisPublish(rs.getString(4));
                szlThesis.setThesisStatus(rs.getString(5));
                szlThesis.setThesisTime(rs.getDate(6));
                szlThesis.setIndexType(rs.getString(7));
                szlThesis.setThesisBelong(rs.getString(8));
                szlThesis.setThesisProof(rs.getString(9));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(con,psmt,rs);
        }
        return szlThesis;
    }

    @Override
    public List<SzlThesis> selectList(SzlThesis szlThesis) {
        String sql = "select * from szl_thesis where 1=1";
        List<SzlThesis> list = new ArrayList<>();
        List<Object> param = new ArrayList<>();
        if (szlThesis == null){
            sql = "select * from szl_thesis";
        }else{
            if(szlThesis.getThesisId() != null){
                sql += " and thesis_id = ?";
                param.add(szlThesis.getThesisId());
            }
            if(szlThesis.getThesisName() != null){
                sql += " and thesis_name like \"%\"?\"%\"";
                param.add(szlThesis.getThesisName());
            }
            if(szlThesis.getType() != null){
                sql += " and type = ?";
                param.add(szlThesis.getType());
            }
            if(szlThesis.getThesisPublish() != null){
                sql += " and thesis_publish like \"%\"?\"%\"";
                param.add(szlThesis.getThesisPublish());
            }
            if(szlThesis.getThesisStatus() != null){
                sql += " and thesis_status like \"%\"?\"%\"";
                param.add(szlThesis.getThesisStatus());
            }
            if(szlThesis.getThesisTime() != null){
                sql += " and thesis_time = ?";
                param.add(szlThesis.getThesisTime());
            }
            if(szlThesis.getIndexType() != null){
                sql += " and index_type like \"%\"?\"%\"";
                param.add(szlThesis.getIndexType());
            }
            if(szlThesis.getThesisBelong() != null){
                sql += " and thesis_belong like \"%\"?\"%\"";
                param.add(szlThesis.getThesisBelong());
            }
            if(szlThesis.getThesisProof() != null){
                sql += " and thesis_proof = ?";
                param.add(szlThesis.getThesisProof());
            }
        }
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            for(int i = 0; i < param.size(); i++){
                psmt.setObject(i+1,param.get(i));
            }
            rs = psmt.executeQuery();
            while (rs.next()){
                SzlThesis szlThesis1 = new SzlThesis();
                szlThesis1.setThesisId(rs.getLong(1));
                szlThesis1.setThesisName(rs.getString(2));
                szlThesis1.setType(rs.getInt(3));
                szlThesis1.setThesisPublish(rs.getString(4));
                szlThesis1.setThesisStatus(rs.getString(5));
                szlThesis1.setThesisTime(rs.getDate(6));
                szlThesis1.setIndexType(rs.getString(7));
                szlThesis1.setThesisBelong(rs.getString(8));
                szlThesis1.setThesisProof(rs.getString(9));
                list.add(szlThesis1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(con,psmt,rs);
        }
        return list ;
    }
}
